using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TrainTicket.VO;


namespace TrainTicket.DAO
{
    public class AccountDAO
    {
        private DbConnection _conn;

        public AccountDAO()
        {
            _conn = new DbConnection();
        }
        
        public int CheckAcount(string userName, string password)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
            param[0].Value = userName;

            param[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
            param[1].Value = password;

            return _conn.ExecuteScalar("sp_CheckAccount", param);
        }
        //Check UserName of Account
        public int CheckAccountExisted(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param.Value = userName;

            return _conn.ExecuteScalar("sp_CheckAccountExisted",param);
        }
        //Insert Account
        public int InsertAccount(string userName,string passWord,int role,string address,string qualification,int age,string phoneNumber,string fullName)
        {
            SqlParameter[] param= new SqlParameter[8];
            param[0] = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param[0].Value = userName;
            param[1] = new SqlParameter("@Password",SqlDbType.NVarChar,50);
            param[1].Value = passWord;
            param[2] = new SqlParameter("@Role",SqlDbType.Int,1);
            param[2].Value = role;
            param[3] = new SqlParameter("@Address",SqlDbType.NVarChar,50);
            param[3].Value = address;
            param[4] = new SqlParameter("@Qualification",SqlDbType.NVarChar,50);
            param[4].Value = qualification;
            param[5] = new SqlParameter("@Age",SqlDbType.Int,1);
            param[5].Value = age;
            param[6] = new SqlParameter("@PhoneNumber",SqlDbType.NVarChar,50);
            param[6].Value = phoneNumber;
            param[7] = new SqlParameter("@FullName",SqlDbType.NVarChar,50);
            param[7].Value = fullName;

            return _conn.ExecuteNonQuery("sp_InsertAccount", param);
        }

        //Update Data
        public int UpdateAccount(int accountID,string userName, int role, string address, string qualification, int age, string phoneNumber, string fullName) 
        {

            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@AccountID", SqlDbType.Int, 1);
            param[0].Value = accountID;
            param[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
            param[1].Value = userName;
            //param[2] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
            //param[2].Value = passWord;
            param[2] = new SqlParameter("@Role", SqlDbType.Int, 1);
            param[2].Value = role;
            param[3] = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
            param[3].Value = address;
            param[4] = new SqlParameter("@Qualification", SqlDbType.NVarChar, 50);
            param[4].Value = qualification;
            param[5] = new SqlParameter("@Age", SqlDbType.Int, 1);
            param[5].Value = age;
            param[6] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar, 50);
            param[6].Value = phoneNumber;
            param[7] = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
            param[7].Value = fullName;

            return _conn.ExecuteNonQuery("sp_UpdateAccount", param);
        }
        //Get All Data
        public DataTable GetAllAccount() 
        {
            return _conn.ExecuteDataTable("sp_GelAllAccount",null);
        }
        //Get Data by Accountid
        public DataTable GetDataByID(int accountID)
        {
            SqlParameter param = new SqlParameter("@AccountID",SqlDbType.Int,1);
            param.Value = accountID;
            return _conn.ExecuteDataTable("sp_GetAccountIDByAccountID",param);
        }
        //GetData by username
        public DataTable GetProfileByUserName(string userName) 
        {
            SqlParameter param = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param.Value = userName;

            return _conn.ExecuteDataTable("sp_GetAllDataByUserName", param);
        }

        //Delete
        public int DeleteAccount(int accountID)
        {
            SqlParameter param = new SqlParameter("@AccountID",SqlDbType.Int,1);
            param.Value = accountID;

            return _conn.ExecuteNonQuery("sp_DeleteAccount",param);
           
        }

        //search Account by username
        public DataTable GetDataByUserName(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param.Value = userName;

            return _conn.ExecuteDataTable("sp_SearchAccount",param);
        }

        //search Account by address
        public DataTable GetDataByAddress(string address)
        {
            SqlParameter param = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
            param.Value = address;

            return _conn.ExecuteDataTable("sp_SearchAccountByAddress", param);
        }

        //search Account by Qualification
        public DataTable GetDataByQualification(string qualification)
        {
            SqlParameter param = new SqlParameter("@Qualification", SqlDbType.NVarChar, 50);
            param.Value = qualification;

            return _conn.ExecuteDataTable("sp_SearchAccountByQualification", param);
        }
        //search Account by PhoneNumber
        public DataTable GetDataByPhoneNumber(string phoneNumber)
        {
            SqlParameter param = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar, 50);
            param.Value = phoneNumber;

            return _conn.ExecuteDataTable("sp_SearchAccountByPhoneNumber", param);
        }

        //search Account by FullName
        public DataTable GetDataByFullName(string fullName)
        {
            SqlParameter param = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
            param.Value = fullName;

            return _conn.ExecuteDataTable("sp_SearchAccountByFullName", param);
        }

        //GetPassword by username
        public DataTable GetPasswordByUserName(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param.Value = userName;

            return _conn.ExecuteDataTable("sp_GetPasswordByUserName", param);
        }

        //Get Role For username
        public DataTable GetRoleForUserName(string userName)
        {
            SqlParameter param = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param.Value = userName;

            return _conn.ExecuteDataTable("sp_GetRoleForUserName",param);
        }

        //Change Pass
        public int ChangePassword(string userName,string password)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            param[0].Value = userName;
            param[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
            param[1].Value = password;
            return _conn.ExecuteNonQuery("sp_ChangePass", param);
        }

    }
}
